/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package junchangmis.teacher;

/**
 *
 * @author developers
 */
import java.util.logging.Level;
import java.util.logging.Logger;
import publicmethod.*;
import junchangmis.codecSetting.*;

import java.util.StringTokenizer;
import java.io.File;
import java.io.IOException;
import java.util.Locale;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import java.sql.*;
import java.util.Vector;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.PageOrientation;
import jxl.format.VerticalAlignment;

public class wageTableExcel {

    private WritableCellFormat timesBoldUnderline;
    private WritableCellFormat times;
    private WritableCellFormat timesOne;
    private WritableCellFormat timesTwo;
    private WritableCellFormat timesTop;
    private WritableCellFormat timesBoldHead;
    private String inputFile;
    Vector<teacher> tea;
    String ym;
    int[] sums = new int[2];

    public wageTableExcel(Vector<teacher> tea, String ym) throws WriteException, IOException {
        this.tea = tea;
        this.ym = ym;
        for (int i = 0; i < sums.length; i++) {
            sums[i] = 0;
        }
    }

    public void setOutputFile(String inputFile) {
        this.inputFile = inputFile;
    }

    public void write() throws IOException, WriteException {
        File file = new File(inputFile);
        WorkbookSettings wbSettings = new WorkbookSettings();

        wbSettings.setLocale(new Locale("en", "CN"));

        WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
        workbook.createSheet("Report", 0);
        WritableSheet excelSheet = workbook.getSheet(0);
        //设置表格打印属性
        excelSheet.getSettings().setTopMargin(0.3);
        excelSheet.getSettings().setLeftMargin(0.1);
        excelSheet.getSettings().setRightMargin(0.1);
        excelSheet.getSettings().setBottomMargin(0.1);
        excelSheet.getSettings().setHorizontalCentre(true);
        excelSheet.getSettings().setOrientation(PageOrientation.LANDSCAPE);

        createLabel(excelSheet);
        createContent(excelSheet);

        workbook.write();
        workbook.close();
    }

    private void createLabel(WritableSheet sheet)
            throws WriteException {
        WritableFont times16ptBoldHead = new WritableFont(WritableFont.TIMES, 18);
        timesBoldHead = new WritableCellFormat(times16ptBoldHead);
        timesBoldHead.setAlignment(Alignment.CENTRE);
        timesBoldHead.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesBoldHead.setWrap(true);

        WritableFont times10pt = new WritableFont(WritableFont.TIMES, 12);
        times = new WritableCellFormat(times10pt);
        times.setAlignment(Alignment.CENTRE);
        times.setVerticalAlignment(VerticalAlignment.CENTRE);
        times.setBorder(Border.ALL, BorderLineStyle.THIN);
        times.setWrap(true);

        timesOne = new WritableCellFormat(times10pt);
        timesOne.setAlignment(Alignment.CENTRE);
        timesOne.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesOne.setWrap(true);

        timesTop = new WritableCellFormat(times10pt);
        timesTop.setAlignment(Alignment.CENTRE);
        timesTop.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesTop.setWrap(true);

        timesTwo = new WritableCellFormat(times10pt);
        timesTwo.setAlignment(Alignment.LEFT);
        timesTwo.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesTwo.setBorder(Border.LEFT, BorderLineStyle.THIN);
        timesTwo.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
        timesTwo.setBorder(Border.RIGHT, BorderLineStyle.THIN);
        timesTwo.setWrap(true);


        WritableFont times10ptBoldUnderline = new WritableFont(
                WritableFont.TIMES, 12, WritableFont.BOLD, false);
        timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
        timesBoldUnderline.setAlignment(Alignment.CENTRE);
        timesBoldUnderline.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesBoldUnderline.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
        timesBoldUnderline.setWrap(true);

        CellView cv = new CellView();
        cv.setFormat(times);
        cv.setFormat(timesBoldUnderline);
        cv.setAutosize(true);

        // Write a few headers

        sheet.mergeCells(0, 0, 9, 0);
        addHeader(sheet, 0, 0, "军昌文化艺术学校教师考勤及工资一览表");
        sheet.setRowView(0, 700);

        sheet.mergeCells(0, 1, 9, 1);
        addLabelTop(sheet, 0, 1, "工资月份：" + ym + "        考勤管理者：                " + "报告日期：        年        月        日");
        sheet.setRowView(1, 400);

        this.addCaption(sheet, 0, 2, "起止时间");
        this.addCaption(sheet, 1, 2, "周数");
        this.addCaption(sheet, 2, 2, "教师姓名");
        this.addCaption(sheet, 3, 2, "班级编码");
        this.addCaption(sheet, 4, 2, "班级名称");
        this.addCaption(sheet, 5, 2, "上课总次数");
        this.addCaption(sheet, 6, 2, "听课总次数");
        this.addCaption(sheet, 7, 2, "工资金额");
        this.addCaption(sheet, 8, 2, "奖金金额");
        this.addCaption(sheet, 9, 2, "领取人签字");


        sheet.setColumnView(0, 24);
        sheet.setColumnView(1, 6);
        sheet.setColumnView(2, 12);
        sheet.setColumnView(3, 12);
        sheet.setColumnView(4, 20);
        sheet.setColumnView(5, 14);
        sheet.setColumnView(6, 14);
        sheet.setColumnView(7, 15);
        sheet.setColumnView(8, 15);
        sheet.setColumnView(9, 15);




    }

    private void createContent(WritableSheet sheet) throws WriteException,
            RowsExceededException {
        int i;
        for (i = 0; i < tea.size(); i++) {
            String startDate = tea.get(i).yearStart + "." + tea.get(i).monthStart + "." + tea.get(i).dayStart;
            String endDate = tea.get(i).yearEnd + "." + tea.get(i).monthEnd + "." + tea.get(i).dayEnd;
            addLabel(sheet, 0, i + 3, startDate + "-" + endDate);

            addLabel(sheet, 1, i + 3, tea.get(i).week);
            addLabel(sheet, 2, i + 3, tea.get(i).name);
            addLabel(sheet, 3, i + 3, tea.get(i).classCode);
            addLabel(sheet, 4, i + 3, codecMethodNew.getMeaning(tea.get(i).classCode));

            addLabel(sheet, 5, i + 3, tea.get(i).classCount);
            sums[0] = sums[0] + Integer.valueOf(tea.get(i).classCount);

            addLabel(sheet, 6, i + 3, tea.get(i).listenCount);
            sums[1] = sums[1] + Integer.valueOf(tea.get(i).listenCount);

            addLabel(sheet, 7, i + 3, "");
            addLabel(sheet, 8, i + 3, "");
            addLabel(sheet, 9, i + 3, "");

        }
        addLabelOne(sheet, 0, i + 3, "总计");
        addLabelOne(sheet, 5, i + 3, sums[0] + "");
        addLabelOne(sheet, 6, i + 3, sums[1] + "");

        for (int j = 2; j < i + 4; j++) {
            sheet.setRowView(j, 400);
        }
    }

    private void addHeader(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesBoldHead);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addCaption(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesBoldUnderline);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addLabelTop(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesTop);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addLabelOne(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesOne);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addLabelTwo(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesTwo);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addNumber(WritableSheet sheet, int column, int row,
            Integer integer) throws WriteException, RowsExceededException {
        Number number;
        number = new Number(column, row, integer, times);//位置 内容 格式
        sheet.addCell(number);
    }

    private void addLabel(WritableSheet sheet, int column, int row, String s)
            throws WriteException, RowsExceededException {
        Label label;
        label = new Label(column, row, s, times);
        sheet.addCell(label);
    }

    public static void main(String[] args) throws WriteException, IOException {
        /* newComerTableExcel test = new newComerTableExcel("SSY111");
        test.setOutputFile("lars.xls");
        test.write();
        System.out.println("Please check the result file under lars.xls ");*/
    }
}
